﻿using System;

using System.Collections;
using System.Data;
using ASBCC.Database.EntityFramework;
using BEGDC.CC.BL;
using BEGDC.CC.Entity;
using BEGDC.Utility.ExceptionManagement;
using Castle.Facilities.NHibernateIntegration;
using Castle.Services.Transaction;
using NHibernate;
using NHibernate.Expression;

using System.Collections.Generic;
using System.Linq;
using System.Text;


namespace BEGDC.CC.BL.Common
{
    /// <summary>
    /// Summary description for UitlityService.
    /// </summary>
    public class UtilityService : IUtility
    {
        private ISessionManager _sessionManager;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sessionManager"></param>
        public UtilityService(ISessionManager sessionManager)
        {
            this._sessionManager = sessionManager;
        }

        #region IUtility Members

        public IList GetMember(int AppId, string role)
        {
            IList list = null;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    string sql = " from FlowMember where AppID=" + AppId.ToString() + " AND RoleID in( select r.Id from Role r where r.RoleName ='" + role + "')";
                    list = session.Find(sql);

                }
                catch (Exception exp)
                {
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return list;

        }

        public DataTable GetRoleMemberList(string UserRole, string type)
        {
            string sql = "";
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    if (type == null || type == "")
                    {
                        sql = "Select distinct empl.EmployeeNo AS EmployeeNo, empl.NameEN AS Name ";
                        sql += "From UserRole AS ur, Employee AS empl, Role AS rl ";
                        sql += "where empl.IsActived='1' and ur.EmployeeNo=empl.EmployeeNo and ur.RoleID=rl.RoleID and rl.RoleName='" + UserRole + "'";
                        sql += " order by Name ";
                    }
                    else
                    {
                        sql = "Select distinct empl.EmployeeNo AS EmployeeNo, empl.NameEN AS Name ";
                        sql += "From UserRole AS ur, Employee AS empl, Role AS rl, Department de ";
                        sql += "where empl.IsActived='1' and ur.EmployeeNo=empl.EmployeeNo and ur.RoleID=rl.RoleID  and rl.RoleName='" + UserRole + "'";
                        sql += " and de.DepartmentID='" + type + "' and empl.Department = de.departmentid";
                        sql += " order by Name ";
                    }
                    return BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, sql);
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message); throw ex;
                }
                finally
                {
                    session.Close();
                }
            }
        }

        public DataTable GetDepartmentFNC(string department)
        {
            string sql = "";
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    sql = "Select empl.EmployeeNo AS EmployeeNo, empl.NameEN AS Name ";
                    sql += "From UserRole AS ur, Employee AS empl, Role AS rl, FNCForDepartment fd ";
                    sql += "where empl.IsActived='1' and ur.EmployeeNo=empl.EmployeeNo and ur.RoleID=rl.RoleID  and rl.RoleName='" + BEGDC.CC.BL.Workflow.UserRole.FNC + "'";
                    sql += " and fd.DepartmentID='" + department + "' and ur.UserRoleID = fd.UserRoleID";

                    return BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, sql);
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message); throw ex;
                }
                finally
                {
                    session.Close();
                }
            }
        }

        public DataTable GetDepartmentFNCByCode(string department)
        {
            string sql = "";
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    sql = "Select empl.EmployeeNo AS EmployeeNo, empl.NameEN AS Name ";
                    sql += "From UserRole AS ur, Employee AS empl, Role AS rl, FNCForDepartment fd, Department d ";
                    sql += "where empl.IsActived='1' and ur.EmployeeNo=empl.EmployeeNo and ur.RoleID=rl.RoleID  and rl.RoleName='" + BEGDC.CC.BL.Workflow.UserRole.FNC + "'";
                    sql += " and fd.DepartmentID=d.DepartmentID and d.DepartmentCode='" + department + "' and ur.UserRoleID = fd.UserRoleID";

                    return BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, sql);
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message); throw ex;
                }
                finally
                {
                    session.Close();
                }
            }
        }

        /// <summary>
        /// Get enabled dictionary items
        /// </summary>
        /// <param name="dicType"></param>
        /// <returns></returns>
        [Transaction(TransactionMode.Requires)]
        public System.Collections.IList BuildDicList(string dicType)
        {
            IList diclist = null;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    diclist = session.Find("FROM DicItem WHERE IsEnabled=1 and DicTypeID IN (SELECT t.Id FROM DicType as t WHERE t.DicTypeKey='" + dicType + "') ORDER BY Sort, DicItemKey");
                }
                catch (Exception exp)
                {
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return diclist;
        }

        public System.Collections.IList GetAllDicItem(string dicType)
        {
            IList diclist = null;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    diclist = session.Find("FROM DicItem WHERE DicTypeID IN (SELECT t.Id FROM DicType as t WHERE t.DicTypeKey='" + dicType + "') ORDER BY DicItemKey");
                }
                catch (Exception exp)
                {
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return diclist;
        }

        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [Transaction(TransactionMode.Requires)]
        public DicItem GetDicItem(int id)
        {
            DicItem dic = null;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    dic = (DicItem)session.Get(typeof(DicItem), id);

                }
                catch (Exception exp)
                {
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return dic;
        }

        /// <summary>
        /// Get a DicItem's ID by it's type and DicItemKey
        /// </summary>
        /// <param name="keyItemValue"></param>
        /// <param name="keyTypeValue"></param>
        /// <returns>id</returns>
        /// Add by Joe.Feng 2006-10-17
        [Transaction(TransactionMode.Requires)]
        public int GetDicItemID(string keyItemValue, string keyTypeValue)
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    IList list = session.Find(" FROM DicItem WHERE DicTypeID = " + GetDicTypeID(keyTypeValue) + " AND DicItemKey = '" + keyItemValue + "'");
                    if (list.Count > 0)
                    {
                        BEGDC.CC.Entity.DicItem dic = list[0] as BEGDC.CC.Entity.DicItem;
                        return dic.Id;
                    }
                    else
                    {
                        return 0;
                    }
                }
                catch (HibernateException ex)
                {
                    return -1;
                }
                finally
                {
                    session.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="keyItemValue"></param>
        /// <param name="keyTypeValue"></param>
        /// <returns></returns>
        [Transaction(TransactionMode.Requires)]
        public DicItem GetDicItem(string keyItemValue, string keyTypeValue)
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    BEGDC.CC.Entity.DicItem dic = session.Find(" FROM DicItem WHERE DicTypeID = " + GetDicTypeID(keyTypeValue) + " AND DicItemKey = '" + keyItemValue + "'")[0] as BEGDC.CC.Entity.DicItem;
                    return dic;
                }
                catch (HibernateException ex)
                {
                    return null;
                }
                finally
                {
                    session.Close();
                }
            }

        }

        public Entity.DicType GetDitTypeByDicItemKey(string itemKey)
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    BEGDC.CC.Entity.DicItem item = session.Find(" FROM DicItem WHERE DicItemKey = '" + itemKey + "'")[0] as BEGDC.CC.Entity.DicItem;
                    if (item != null)
                        return item.DicType;
                    return null;
                }
                catch (HibernateException ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message);
                    throw new GDCException(ErrorCode.DB_Exception, ex.Message, ex);
                }
                finally
                {
                    session.Close();
                }
            }
        }

        /// <summary>
        /// Get a DicType's ID by it's type
        /// </summary>
        /// <param name="keyTypeValue"></param>
        /// <param name="sessionManager"></param>
        /// <returns></returns>
        /// Add by Joe.Feng 2006-10-17
        [Transaction(TransactionMode.Requires)]
        public int GetDicTypeID(string keyTypeValue)
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    IList list = session.Find(" FROM DicType AS dt WHERE dt.DicTypeKey = '" + keyTypeValue + "'");
                    if (list != null && list.Count > 0)
                    {
                        BEGDC.CC.Entity.DicType type = list[0] as BEGDC.CC.Entity.DicType;
                        return type.Id;
                    }
                    return -1;
                }
                catch (HibernateException ex)
                {
                    return -1;
                }
                finally
                {
                    session.Close();
                }
            }
        }

        [Transaction(TransactionMode.Requires)]
        private int GetDicTypeID(ISession session, string keyTypeValue)
        {
            try
            {
                BEGDC.CC.Entity.DicType type = session.Find(" FROM DicType WHERE DicTypeKey = '" + keyTypeValue + "'")[0] as BEGDC.CC.Entity.DicType;
                return type.Id;
            }
            catch (HibernateException ex)
            {
                return -1;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        [Transaction(TransactionMode.Requires)]
        public void UpdateCustomerAttendee(int attendeeId)
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    string cmdText = "Update CustomerAttendee SET IsExisted = 2, ClearDate = '" + System.DateTime.Now + "' WHERE AttendeeId = " + attendeeId;
                    BEGDC.Utility.NHibernate.NHibernateUtil.ExecuteNonQuery(session, cmdText);
                }
                catch (Exception exp)
                {
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }

            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="typeValue"></param>
        /// <returns></returns>
        public BEGDC.CC.Entity.DicType GetDicType(string typeValue)
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    BEGDC.CC.Entity.DicType type = session.Find(" FROM DicType WHERE DicTypeKey = '" + typeValue + "'")[0] as BEGDC.CC.Entity.DicType;
                    return type;
                }
                catch (HibernateException ex)
                {
                    return null;
                }
                finally
                {
                    session.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dicType"></param>
        /// <returns></returns>
        [Transaction(TransactionMode.Requires)]
        public IList GetDicType()
        {
            IList diclist = null;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    diclist = session.Find("FROM DicType where IsSys = 0 order by DicTypeKey");
                }
                catch (Exception exp)
                {
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return diclist;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dicType"></param>
        /// <returns></returns>
        [Transaction(TransactionMode.Requires)]
        public IList GetAllDicType()
        {
            IList diclist = null;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    diclist = session.Find("FROM DicType");
                }
                catch (Exception exp)
                {
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return diclist;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dicType"></param>
        /// <returns></returns>
        [Transaction(TransactionMode.Requires)]
        public int AddDicType(BEGDC.CC.Entity.DicType type)
        {
            IList diclist = null;
            int operationResult = 1;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {

                    diclist = session.Find("FROM DicType where DicTypeKey='" + type.DicTypeKey + "'");
                    if (diclist != null && diclist.Count > 0)
                        operationResult = -1;
                    else
                    {
                        string sql = " select COALESCE (max(dictypeid),0) from dictype ";
                        System.Data.DataTable dtMax = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, sql);
                        int max = Convert.ToInt32(dtMax.Rows[0][0]);

                        max++;

                        type.Id = max;
                        session.Save(type, max);
                        session.Flush();
                    }
                }
                catch (Exception exp)
                {
                    operationResult = 0;
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return operationResult;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dicType"></param>
        /// <returns></returns>
        [Transaction(TransactionMode.Requires)]
        public int AddDicItem(BEGDC.CC.Entity.DicItem item)
        {
            IList diclist = null;
            int operationResult = 1;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {

                    diclist = session.Find("FROM DicItem where DicTypeID=" + item.DicType.Id + " and DicItemKey ='" + item.DicItemKey.Replace("'", "''") + "'");
                    if (diclist != null && diclist.Count > 0)
                        operationResult = -1;
                    else
                    {
                        string sql = " select COALESCE (max(dicitemid),0) from dicitem ";
                        System.Data.DataTable dtMax = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, sql);
                        int max = Convert.ToInt32(dtMax.Rows[0][0]);

                        max++;

                        item.Id = max;

                        session.Save(item, max);
                        session.Flush();
                    }
                }
                catch (Exception exp)
                {
                    operationResult = 0;
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return operationResult;
        }
        [Transaction(TransactionMode.Requires)]
        public bool UpdateAttachment(Attachment attachOld, Attachment attachNew)
        {
            bool operationResult = true;
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    //check if there are other attachment using the file
                    string sql = "SELECT FileID FROM Attachment WHERE FilePath='" + attachOld.FilePath + "'";
                    DataTable dt = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, sql);
                    if (dt == null || dt.Rows.Count == 0)
                    {
                        if (System.IO.File.Exists(attachOld.FilePath))
                            System.IO.File.Delete(attachOld.FilePath);
                    }
                    session.Update(attachNew);
                    session.Flush();
                }
                catch
                {
                    operationResult = false;
                }
                finally
                {
                    session.Close();
                }
            }
            return operationResult;

        }

        [Transaction(TransactionMode.Requires)]
        public bool DeleteApplication(int appID)
        {
            bool operationResult = false;
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    //check if there are other attachment using the file
                    string sql = "Update FlowInstance set State = 12 where appid =" + appID;
                    int result = BEGDC.Utility.NHibernate.NHibernateUtil.ExecuteNonQuery(session, sql);
                    if (result == 1)
                    {
                        operationResult = true;
                    }
                }
                catch
                {
                    operationResult = false;
                }
                finally
                {
                    session.Close();
                }
            }
            return operationResult;

        }

        [Transaction(TransactionMode.Requires)]
        public bool DeleteDraftApplication(int appID)
        {
            bool operationResult = false;
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    //check if there are other attachment using the file
                    string sql = "exec deletedraftapplication " + appID;
                    int result = BEGDC.Utility.NHibernate.NHibernateUtil.ExecuteNonQuery(session, sql);
                    if (result >= 1)
                    {
                        operationResult = true;
                    }
                }
                catch (Exception ex)
                {
                    operationResult = false;
                }
                finally
                {
                    session.Close();
                }
            }
            return operationResult;
        }

        [Transaction(TransactionMode.Requires)]
        public bool UpdateTemplate(Template tempOld, Template tempNew)
        {
            bool operationResult = true;
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    if (System.IO.File.Exists(tempOld.FilePath))
                        System.IO.File.Delete(tempOld.FilePath);
                    session.Update(tempNew);
                    session.Flush();
                }
                catch
                {
                    operationResult = false;
                }
                finally
                {
                    session.Close();
                }
            }
            return operationResult;

        }
        public bool SaveTemplate(BEGDC.CC.Entity.Template template)
        {
            bool operationResult = true;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    session.Save(template);
                }
                catch
                {
                    operationResult = false;
                }
                finally
                {
                    session.Close();
                }
            }

            return operationResult;
        }
        public bool SaveAttachment(BEGDC.CC.Entity.Attachment attach)
        {
            bool operationResult = true;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    session.Save(attach);
                }
                catch (Exception ex)
                {
                    operationResult = false;
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message); throw ex;
                }
                finally
                {
                    session.Close();
                }
            }

            return operationResult;
        }

        [Transaction(TransactionMode.Requires)]
        public bool SaveDicType(BEGDC.CC.Entity.DicType type)
        {
            bool operationResult = true;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    BEGDC.CC.Entity.DicType exist = this.GetDicType(type.Id);
                    if (exist != null)
                    {
                        exist.DicTypeKey = type.DicTypeKey;
                        //						exist.IsEnabled = type.IsEnabled;
                        exist.IsSys = type.IsSys;
                        session.Update(exist);
                        session.Flush();
                    }
                    else
                        operationResult = false;
                }
                catch (Exception exp)
                {
                    operationResult = false;
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return operationResult;
        }



        /// <summary>
        /// 
        /// </summary>
        /// <returns>IList</returns>
        public BEGDC.CC.Entity.DicType GetDicType(int id)
        {
            BEGDC.CC.Entity.DicType dic = null;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    IList diclist = session.Find("FROM DicType where DicTypeID =" + id.ToString());
                    if (diclist != null && diclist.Count > 0)
                        dic = (BEGDC.CC.Entity.DicType)diclist[0];
                }
                catch (Exception exp)
                {
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return dic;
        }

        public int SaveDicItem(BEGDC.CC.Entity.DicItem item)
        {
            int operationResult = 1;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    BEGDC.CC.Entity.DicItem exist = this.GetDicItem(item.Id);
                    if (exist != null)
                    {
                        if (item.IsDefault)
                        {
                            IList diclist = session.Find("FROM DicItem where DicTypeID =" + exist.DicType.Id.ToString() + " and IsDefault=1");
                            if (diclist != null && diclist.Count > 0)
                            {
                                DicItem dic = diclist[0] as DicItem; ;
                                dic.IsDefault = false;
                                session.Update(exist);
                                session.Flush();
                            }
                        }
                        exist.DicItemKey = item.DicItemKey;
                        exist.Sort = item.Sort;
                        exist.IsEnabled = item.IsEnabled;
                        exist.IsDefault = item.IsDefault;
                        session.Update(exist);
                        session.Flush();
                    }
                    else
                        operationResult = 0;
                }
                catch (Exception exp)
                {
                    operationResult = 0;
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return operationResult;
        }

        /// <summary>
        /// Add a new log
        /// </summary>
        /// <param name="type"></param>
        /// <returns>int</returns>
        public bool SaveOperationLog(BEGDC.CC.Entity.OperationLog log)
        {
            bool operationResult = true;
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    session.Save(log);
                }
                catch (Exception exp)
                {
                    operationResult = false;
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }
            return operationResult;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns>IList</returns>
        public DataTable GetLoginoutLog(string start, string end)
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    if (start == string.Empty)
                        start = System.DateTime.Now.Date.ToShortDateString() + " 0:00:00";
                    else
                        start += " 0:00:00";

                    if (end == string.Empty)
                        end = System.DateTime.Now.Date.ToShortDateString() + " 23:59:59";
                    else
                        end += " 23:59:59";

                    string query = "SELECT      COALESCE (Employee.NameCN, OperationLog.EmployeeNo) AS OperatorName, OperationLog.ID, OperationLog.EmployeeNo, OperationLog.Time, OperationLog.Module,          OperationLog.Description, OperationLog.AppNO, OperationLog.CurrentStep, OperationLog.OperationType,    Department.DepartmentCode AS Department FROM         OperationLog LEFT OUTER JOIN  Employee ON OperationLog.EmployeeNo = Employee.EmployeeNo  LEFT OUTER JOIN  Department ON Employee.Department = Department.DepartmentID where (OperationLog.description like 'Logout%' or OperationLog.description like 'Login%') or [Time] between '" + start + "' and '" + end + "' and AppNO is null order by time desc";

                    return BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, query);
                }
                catch (GDCException gdcEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(gdcEx.StackTrace, gdcEx.Message);
                    throw gdcEx;
                }
                catch (System.Data.SqlClient.SqlException sqlEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(sqlEx.StackTrace, sqlEx.Message);
                    throw new GDCException(ErrorCode.DB_Exception, "DB Exception", sqlEx);
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message);
                    throw new GDCException(ErrorCode.QueryService_Exception, "QueryService Exception", ex);
                }
                finally
                {
                    session.Close();
                }
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns>IList</returns>
        public DataTable GetOperationLog(string start, string end, bool isWorkflowLog)
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    if (start == string.Empty)
                        start = System.DateTime.Now.Date.ToShortDateString() + " 0:00:00";
                    else
                        start += " 0:00:00";

                    if (end == string.Empty)
                        end = System.DateTime.Now.Date.ToShortDateString() + " 23:59:59";
                    else
                        end += " 23:59:59";

                    string query = "SELECT      COALESCE (Employee.NameCN, OperationLog.EmployeeNo) AS OperatorName, OperationLog.ID, OperationLog.EmployeeNo, OperationLog.Time, OperationLog.Module,          OperationLog.Description, OperationLog.AppNO, OperationLog.CurrentStep, OperationLog.OperationType,    Department.DepartmentCode AS Department FROM         OperationLog LEFT OUTER JOIN  Employee ON OperationLog.EmployeeNo = Employee.EmployeeNo  LEFT OUTER JOIN  Department ON Employee.Department = Department.DepartmentID where [Time] between '" + start + "' and '" + end + "' and AppNO is null order by time desc";

                    if (isWorkflowLog)
                        query = "SELECT     COALESCE (Employee.NameCN, OperationLog.EmployeeNo) AS OperatorName, OperationLog.ID, OperationLog.EmployeeNo, OperationLog.Time, OperationLog.Module,          OperationLog.Description, OperationLog.AppNO, OperationLog.CurrentStep, OperationLog.OperationType,    Department.DepartmentCode AS Department FROM         OperationLog LEFT OUTER JOIN  Employee ON OperationLog.EmployeeNo = Employee.EmployeeNo  LEFT OUTER JOIN  Department ON Employee.Department = Department.DepartmentID where [Time] between '" + start + "' and '" + end + "' and AppNO is not null order by time desc";

                    return BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, query);
                }
                catch (GDCException gdcEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(gdcEx.StackTrace, gdcEx.Message);
                    throw gdcEx;
                }
                catch (System.Data.SqlClient.SqlException sqlEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(sqlEx.StackTrace, sqlEx.Message);
                    throw new GDCException(ErrorCode.DB_Exception, "DB Exception", sqlEx);
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message);
                    throw new GDCException(ErrorCode.QueryService_Exception, "QueryService Exception", ex);
                }
                finally
                {
                    session.Close();
                }
            }
        }

        /// <summary>
        /// Get user by userid
        /// </summary>
        /// <param name="UserID">userid</param>
        /// <returns>user object</returns>
        [Transaction(TransactionMode.Requires)]
        public Step QueryStepByCode(string code, int type)
        {
            Step step = null;
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    IList list = session.Find("from Step where StepCode ='" + code + "' and AppTypeID=" + type);
                    if (list.Count > 0)
                        step = (Step)list[0];
                }
                catch (GDCException gdcEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(gdcEx.StackTrace, gdcEx.Message);
                    throw gdcEx;
                }
                catch (System.Data.SqlClient.SqlException sqlEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(sqlEx.StackTrace, sqlEx.Message);
                    throw new GDCException(ErrorCode.DB_Exception, "DB Exception", sqlEx);
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message);
                    throw new GDCException(ErrorCode.UserService_Exception, "UserService Exception", ex);
                }
                finally
                {
                    session.Close();
                }
            }
            return step;
        }

        /// <summary>
        /// Get user by userid
        /// </summary>
        /// <param name="UserID">userid</param>
        /// <returns>user object</returns>
        [Transaction(TransactionMode.Requires)]
        public State QueryStateByCode(string code)
        {
            State state = null;
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    IList list = session.Find("from State where StateCode ='" + code + "'");
                    if (list.Count > 0)
                        state = (State)list[0];
                }
                catch (GDCException gdcEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(gdcEx.StackTrace, gdcEx.Message);
                    throw gdcEx;
                }
                catch (System.Data.SqlClient.SqlException sqlEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(sqlEx.StackTrace, sqlEx.Message);
                    throw new GDCException(ErrorCode.DB_Exception, "DB Exception", sqlEx);
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message);
                    throw new GDCException(ErrorCode.UserService_Exception, "UserService Exception", ex);
                }
                finally
                {
                    session.Close();
                }
            }
            return state;
        }

        [Transaction(TransactionMode.Requires)]
        public IList GetState()
        {
            IList list = null;
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    list = session.Find("from State");
                    return list;
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message); throw ex;
                }
                finally
                {
                    session.Close();
                }
            }
        }

        #region Legal Entity

        [Transaction(TransactionMode.Requires)]
        public bool SaveLegalEntity(BEGDC.CC.Entity.LegalEntity type)
        {
            bool operationResult = true;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    BEGDC.CC.Entity.LegalEntity exist = this.GetLegalEntity(type.Id);
                    if (exist != null)
                    {
                        exist.LegalEntityName = type.LegalEntityName;
                        exist.IsEnabled = type.IsEnabled;
                        exist.Description = type.Description;
                        session.Update(exist);
                        session.Flush();
                    }
                    else
                        operationResult = false;
                }
                catch (Exception exp)
                {
                    operationResult = false;
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return operationResult;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns>IList</returns>
        public BEGDC.CC.Entity.LegalEntity GetLegalEntity(int id)
        {
            BEGDC.CC.Entity.LegalEntity dic = null;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    IList diclist = session.Find("FROM LegalEntity where LegalEntityID =" + id.ToString());
                    if (diclist != null && diclist.Count > 0)
                        dic = (BEGDC.CC.Entity.LegalEntity)diclist[0];
                }
                catch (Exception exp)
                {
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return dic;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="LegalEntity"></param>
        /// <returns></returns>
        [Transaction(TransactionMode.Requires)]
        public int AddLegalEntity(BEGDC.CC.Entity.LegalEntity type)
        {
            IList diclist = null;
            int operationResult = 1;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {

                    diclist = session.Find("FROM LegalEntity where LegalEntityName='" + type.LegalEntityName + "'");
                    if (diclist != null && diclist.Count > 0)
                        operationResult = -1;
                    else
                    {
                        string sql = " select COALESCE (max(LegalEntityid),0) from LegalEntity ";
                        System.Data.DataTable dtMax = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, sql);
                        int max = Convert.ToInt32(dtMax.Rows[0][0]);

                        max++;

                        type.Id = max;
                        session.Save(type, max);
                        session.Flush();
                    }
                }
                catch (Exception exp)
                {
                    operationResult = 0;
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return operationResult;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="LegalEntity"></param>
        /// <returns></returns>
        [Transaction(TransactionMode.Requires)]
        public IList GetLegalEntity()
        {
            IList diclist = null;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    diclist = session.Find("FROM LegalEntity");
                }
                catch (Exception exp)
                {
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return diclist;
        }


        [Transaction(TransactionMode.Requires)]
        public DataTable GetLegalEntityDataTable()
        {
            DataTable list;
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {

                    string sql = "SELECT     * from LegalEntity";

                    list = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, sql);
                }
                catch (GDCException gdcEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(gdcEx.StackTrace, gdcEx.Message);
                    throw gdcEx;
                }
                catch (System.Data.SqlClient.SqlException sqlEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(sqlEx.StackTrace, sqlEx.Message);
                    throw new GDCException(ErrorCode.DB_Exception, "DB Exception", sqlEx);
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message);
                    throw new GDCException(ErrorCode.UserService_Exception, "UserService Exception", ex);
                }
                finally
                {
                    session.Close();
                }
            }
            return list;
        }
        #endregion

        //		#region Hotel
        //
        //		[Transaction(TransactionMode.Requires)]
        //		public bool SaveHotel(BEGDC.CC.Entity.Hotel type)
        //		{
        //			bool operationResult = true;
        //	
        //			using(ISession session = _sessionManager.OpenSession())
        //			{
        //				try
        //				{
        //					BEGDC.CC.Entity.Hotel exist = this.GetHotel(type.Id);
        //					if(exist != null)
        //					{
        //						exist.HotelNameCN = type.HotelNameCN;
        //						exist.HotelNameEN = type.HotelNameEN;
        //						exist.Location = type.Location;
        //						exist.StarLevel = type.StarLevel;
        //						exist.Telephone = type.Telephone;
        //						exist.Fax = type.Fax;
        //						exist.Address = type.Address;
        //						
        //						session.Update(exist);
        //						session.Flush();
        //					}
        //					else
        //						operationResult = false;
        //				}
        //				catch( Exception exp )
        //				{
        //					operationResult = false;
        //					CCS.Utility.LogAccess.WriteErrLog( exp.StackTrace, exp.Message );throw exp;
        //				}
        //				finally
        //				{
        //					session.Close();
        //				}
        //			}
        //
        //			return operationResult;
        //		}
        //
        //		/// <summary>
        //		/// 
        //		/// </summary>
        //		/// <returns>IList</returns>
        //		public BEGDC.CC.Entity.Hotel GetHotel(int id)
        //		{
        //			BEGDC.CC.Entity.Hotel dic = null;
        //
        //			using(ISession session = _sessionManager.OpenSession())
        //			{
        //				try
        //				{
        //					IList diclist = session.Find ("FROM Hotel where HotelID ="+id.ToString());
        //					if(diclist != null && diclist.Count > 0)
        //						dic = (BEGDC.CC.Entity.Hotel)diclist[0];
        //				}
        //				catch( Exception exp )
        //				{
        //					CCS.Utility.LogAccess.WriteErrLog( exp.StackTrace, exp.Message );throw exp;
        //				}
        //				finally
        //				{
        //					session.Close();
        //				}
        //			}
        //
        //			return dic;
        //		}
        //
        //		/// <summary>
        //		/// 
        //		/// </summary>
        //		/// <param name="Hotel"></param>
        //		/// <returns></returns>
        //		[Transaction(TransactionMode.Requires)]
        //		public int AddHotel(BEGDC.CC.Entity.Hotel type)
        //		{
        //			IList diclist = null;
        //			int operationResult = 1;
        //	
        //			using(ISession session = _sessionManager.OpenSession())
        //			{
        //				try
        //				{
        //				
        //					diclist = session.Find ("FROM Hotel where HotelName='"+type.HotelNameCN+"'");
        //					if(diclist != null && diclist.Count > 0)
        //						operationResult = -1;
        //					else
        //					{
        //						string sql = " select COALESCE (max(Hotelid),0) from Hotel ";
        //						System.Data.DataTable dtMax = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, sql);
        //						int max = Convert.ToInt32(dtMax.Rows[0][0]);
        //					
        //						max++;
        //
        //						type.Id = max;
        //						session.Save(type,max);
        //						session.Flush();
        //					}
        //				}
        //				catch( Exception exp )
        //				{
        //					operationResult = 0;
        //					CCS.Utility.LogAccess.WriteErrLog( exp.StackTrace, exp.Message );throw exp;
        //				}
        //				finally
        //				{
        //					session.Close();
        //				}
        //			}
        //
        //			return operationResult;
        //		}
        //		/// <summary>
        //		/// 
        //		/// </summary>
        //		/// <param name="Hotel"></param>
        //		/// <returns></returns>
        //		[Transaction(TransactionMode.Requires)]
        //		public IList GetHotel()
        //		{
        //			IList diclist = null;
        //	
        //			using(ISession session = _sessionManager.OpenSession())
        //			{
        //				try
        //				{
        //					diclist = session.Find ("FROM Hotel");
        //				}
        //				catch( Exception exp )
        //				{
        //					CCS.Utility.LogAccess.WriteErrLog( exp.StackTrace, exp.Message );throw exp;
        //				}
        //				finally
        //				{
        //					session.Close();
        //				}
        //			}
        //
        //			return diclist;
        //		}
        //
        //		
        //		[Transaction(TransactionMode.Requires)]
        //		public DataTable GetHotelDataTable()
        //		{
        //			DataTable list;
        //			using(ISession session = _sessionManager.OpenSession())
        //			{
        //				try
        //				{
        //
        //					string sql = "SELECT    *, StartLevel.DicItemKey AS StarLevel, Location.DicItemKey AS Location FROM         Hotel left outer JOIN    DicItem AS StartLevel ON Hotel.StarLevel = StartLevel.DicItemID left outer JOIN     DicItem AS Location ON Hotel.Location = Location.DicItemID";
        //
        //					list = BEGDC.Utility.NHibernate.NHibernateUtil.Execute( session, sql );
        //				}
        //				catch(GDCException gdcEx)
        //				{
        //					throw gdcEx;
        //				}
        //				catch(System.Data.SqlClient.SqlException sqlEx)
        //				{
        //					throw new GDCException(ErrorCode.DB_Exception, "DB Exception", sqlEx);
        //				}
        //				catch(Exception ex)
        //				{
        //					throw new GDCException(ErrorCode.UserService_Exception, "UserService Exception", ex);
        //				}
        //				finally
        //				{
        //					session.Close();
        //				}
        //			}
        //			return list;
        //		}
        //
        //		#endregion 

        #region Currency Exchange
        [Transaction(TransactionMode.Requires)]
        public BEGDC.CC.Entity.ExchangeRate GetCurrencyExchange()
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    BEGDC.CC.Entity.ExchangeRate exchange = session.Find("from ExchangeRate")[0] as BEGDC.CC.Entity.ExchangeRate;
                    return exchange;
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message); throw ex;
                }
                finally
                {
                    session.Close();
                }
            }
        }

        [Transaction(TransactionMode.Requires)]
        public void SaveCurrencyExchange(BEGDC.CC.Entity.ExchangeRate exchange)
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    if (exchange.Id != "")
                    {
                        session.Update(exchange);
                        session.Flush();
                    }
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message); throw ex;
                }
                finally
                {
                    session.Close();
                }
            }
        }
        #endregion

        [Transaction(TransactionMode.Requires)]
        public IList GetALLCATList()
        {
            IList list = null;
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    list = session.Find("select dept from dept in class BEGDC.CC.Entity.Department, dic in class BEGDC.CC.Entity.DicItem where dept.DepartmentType.Id=dic.Id and dic.DicItemKey='CAT'");
                    return list;
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message); throw ex;
                }
                finally
                {
                    session.Close();
                }
            }
        }

        [Transaction(TransactionMode.Requires)]
        public bool DeleteFileById(int fileId)
        {

            bool operationResult = true;
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    if (fileId > 0)
                    {
                        session.Delete("FROM Attachment WHERE FileID = " + fileId);
                        session.Flush();
                    }
                }
                catch (Exception ex)
                {
                    operationResult = false;
                }
                finally
                {
                    session.Close();
                }
            }
            return operationResult;
        }

        [Transaction(TransactionMode.Requires)]
        public bool DeleteTemplateByID(int templateID)
        {
            bool operationResult = true;
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    if (templateID > 0)
                    {
                        session.Delete("FROM Template WHERE TemplateID = " + templateID);
                        session.Flush();
                    }
                }
                catch (Exception ex)
                {
                    operationResult = false;
                }
                finally
                {
                    session.Close();
                }
            }
            return operationResult;
        }

        [Transaction(TransactionMode.Requires)]
        public DataTable GetCODStaffByAppID(int appid, bool isFilter)
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    string sql = string.Empty;
                    string type = string.Empty;
                    DataTable table = null;

                    //Get application type
                    sql = "select ApplicationType from application where appid=" + appid;
                    table = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, sql);
                    if (table != null && table.Rows.Count > 0)
                        type = table.Rows[0][0].ToString();

                    if (isFilter)
                    {
                        //Get existed members
                        //						string cmdText = "select distinct employeeno from flowmember inner join role on flowmember.roleid = role.roleid where appid="+appid + " and role.rolename='" +type+"CODStaff";;
                        //						table = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, sql);
                        //						string currentStaff = "'',";						
                        //
                        //						if(table != null)
                        //						{
                        //							for(int i=0; i<table.Rows.Count; i++)
                        //								if(table.Rows[i][0].ToString() != string.Empty)
                        //									currentStaff += "'" + table.Rows[i][0] + "',";
                        //							currentStaff = currentStaff.TrimEnd(',');
                        //						}						

                        sql = "select e.employeeno, e.nameen from userrole ur inner join role r on ur.roleid = r.roleid inner join Employee e ";
                        if (type == "OCV" || type == "OCR" || type == "EBDCV" || type == "ESCCV")
                        {
                            sql += " on ur.employeeno = e.employeeno where r.rolename='" + type + "Staff' and e.IsEnabled=1 and e.IsActived=1 and ur.employeeno not in(";
                            sql += " select distinct employeeno from flowmember inner join role on flowmember.roleid = role.roleid where appid=" + appid + " and role.rolename='" + type + "Staff'" + ")";
                        }
                        else if (type == "EBD" || type == "ESC")
                        {
                            sql += " on ur.employeeno = e.employeeno where r.rolename='" + type + "CRStaff' and e.IsEnabled=1 and e.IsActived=1 and ur.employeeno not in(";
                            sql += " select distinct employeeno from flowmember inner join role on flowmember.roleid = role.roleid where appid=" + appid + " and role.rolename='" + type + "CRStaff'" + ")";
                        }
                        else
                        {
                            sql += " on ur.employeeno = e.employeeno where r.rolename='" + type + "CODStaff' and e.IsEnabled=1 and e.IsActived=1 and ur.employeeno not in(";
                            sql += " select distinct employeeno from flowmember inner join role on flowmember.roleid = role.roleid where appid=" + appid + " and role.rolename='" + type + "CODStaff'" + ")";
                        }
                    }
                    else
                    {
                        sql = "select e.employeeno, e.nameen from userrole ur inner join role r on ur.roleid = r.roleid inner join Employee e ";
                        if (type == "OCV" || type == "OCR" || type == "EBDCV" || type == "ESCCV")
                            sql += " on ur.employeeno = e.employeeno where r.rolename='" + type + "Staff'";
                        else if (type == "EBD" || type == "ESC")
                            sql += " on ur.employeeno = e.employeeno where r.rolename='" + type + "CRStaff'";
                        else
                            sql += " on ur.employeeno = e.employeeno where r.rolename='" + type + "CODStaff'";
                        sql += " and e.IsEnabled=1 and e.IsActived=1";

                    }
                    return BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, sql);
                }
                catch (GDCException gdcEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(gdcEx.StackTrace, gdcEx.Message);
                    throw gdcEx;
                }
                catch (System.Data.SqlClient.SqlException sqlEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(sqlEx.StackTrace, sqlEx.Message);
                    throw new GDCException(ErrorCode.DB_Exception, "DB Exception", sqlEx);
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message);
                    throw new GDCException(ErrorCode.QueryService_Exception, "QueryService Exception", ex);
                }
                finally
                {
                    session.Close();
                }
            }
        }

        [Transaction(TransactionMode.Requires)]
        public DataTable GetOCOStaffByAppID(int appid, bool isFilter)
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    string sql = string.Empty;
                    string type = string.Empty;
                    DataTable table = null;

                    //Get application type
                    sql = "select ApplicationType from application where appid=" + appid;
                    table = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, sql);
                    if (table != null && table.Rows.Count > 0)
                        type = table.Rows[0][0].ToString();

                    if (type == "EBD" || type == "ESC")
                    {
                        type += "CR";
                    }
                    if (isFilter)
                    {
                        //Get existed members
                        string cmdText = "select distinct employeeno from flowmember inner join role on flowmember.roleid = role.roleid where appid=" + appid + " and role.rolename='" + type + "Staff";

                        sql = "select e.employeeno, e.nameen from userrole ur inner join role r on ur.roleid = r.roleid inner join Employee e ";
                        sql += " on ur.employeeno = e.employeeno where r.rolename='" + type + "Staff' and ur.employeeno not in(";
                        sql += " select distinct employeeno from flowmember inner join role on flowmember.roleid = role.roleid where appid=" + appid + " and role.rolename='" + type + "Staff'" + ")";
                    }
                    else
                    {
                        sql = "select e.employeeno, e.nameen from userrole ur inner join role r on ur.roleid = r.roleid inner join Employee e ";
                        sql += " on ur.employeeno = e.employeeno where r.rolename='" + type + "Staff'";

                    }
                    return BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, sql);
                }
                catch (GDCException gdcEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(gdcEx.StackTrace, gdcEx.Message);
                    throw gdcEx;
                }
                catch (System.Data.SqlClient.SqlException sqlEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(sqlEx.StackTrace, sqlEx.Message);
                    throw new GDCException(ErrorCode.DB_Exception, "DB Exception", sqlEx);
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message);
                    throw new GDCException(ErrorCode.QueryService_Exception, "QueryService Exception", ex);
                }
                finally
                {
                    session.Close();
                }
            }
        }

        [Transaction(TransactionMode.Requires)]
        public string[] DisplayAppAttachments(int AppId, string AttType)
        {
            string[] AttInfo = null;
            IList list = null;
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    list = session.Find("From Attachment as att where att.Application.Id=" + AppId + " and att.AttachmentType.DicItemKey='" + AttType + "'");
                    if (list != null && list.Count > 0)
                    {
                        Entity.Attachment attachment = list[0] as Entity.Attachment;
                        AttInfo = new string[2];
                        AttInfo[0] = attachment.FileName;
                        AttInfo[1] = attachment.FilePath;
                    }
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message); throw ex;
                }
                finally
                {
                    session.Close();
                }
            }
            return AttInfo;
        }

        #region SystemBulletin

        [Transaction(TransactionMode.Requires)]
        public bool SaveSystemBulletin(BEGDC.CC.Entity.SystemBulletin type)
        {
            bool operationResult = true;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    BEGDC.CC.Entity.SystemBulletin exist = this.GetSystemBulletin(type.Id);
                    if (exist != null)
                    {
                        exist.Title = type.Title;
                        //						exist.IsEnabled = type.IsEnabled;
                        exist.Description = type.Description;
                        session.Update(exist);
                        session.Flush();
                    }
                    else
                        operationResult = false;
                }
                catch (Exception exp)
                {
                    operationResult = false;
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return operationResult;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns>IList</returns>
        public BEGDC.CC.Entity.SystemBulletin GetSystemBulletin(int id)
        {
            BEGDC.CC.Entity.SystemBulletin dic = null;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    IList diclist = session.Find("FROM SystemBulletin where BulletinID =" + id.ToString());
                    if (diclist != null && diclist.Count > 0)
                        dic = (BEGDC.CC.Entity.SystemBulletin)diclist[0];
                }
                catch (Exception exp)
                {
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return dic;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="SystemBulletin"></param>
        /// <returns></returns>
        [Transaction(TransactionMode.Requires)]
        public int AddSystemBulletin(BEGDC.CC.Entity.SystemBulletin type)
        {
            IList diclist = null;
            int operationResult = 1;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {

                    diclist = session.Find("FROM SystemBulletin where Title='" + type.Title + "'");
                    if (diclist != null && diclist.Count > 0)
                        operationResult = -1;
                    else
                    {
                        session.Save(type);
                        session.Flush();
                    }
                }
                catch (Exception exp)
                {
                    operationResult = 0;
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return operationResult;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="SystemBulletin"></param>
        /// <returns></returns>
        [Transaction(TransactionMode.Requires)]
        public IList GetSystemBulletin()
        {
            IList diclist = null;

            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    diclist = session.Find("FROM SystemBulletin order by BulletinID desc");
                }
                catch (Exception exp)
                {
                    CCS.Utility.LogAccess.WriteErrLog(exp.StackTrace, exp.Message); throw exp;
                }
                finally
                {
                    session.Close();
                }
            }

            return diclist;
        }

        public void DeleteSystemBulletinById(int bulletinId)
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    if (bulletinId > 0)
                    {
                        session.Delete("FROM SystemBulletin WHERE BulletinID = " + bulletinId);
                        session.Flush();
                    }
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message); throw ex;
                }
                finally
                {
                    session.Close();
                }
            }
        }
        #endregion

        /// <summary>
        /// Province List
        /// </summary>
        [Transaction(TransactionMode.Requires)]
        public DataTable GetProvinces()
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    string cmdText = "select ProvinceID, Name from Province where Disabled = 0 order by Name";
                    DataTable table = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, cmdText);
                    return table;
                }
                catch (GDCException gdcEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(gdcEx.StackTrace, gdcEx.Message);
                    throw gdcEx;
                }
                catch (System.Data.SqlClient.SqlException sqlEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(sqlEx.StackTrace, sqlEx.Message); throw new GDCException(ErrorCode.DB_Exception, "DB Exception", sqlEx);
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message); throw new GDCException(ErrorCode.QueryService_Exception, "QueryService Exception", ex);
                }
                finally
                {
                    session.Close();
                }
            }
        }

        /// <summary>
        /// Category List
        /// </summary>
        [Transaction(TransactionMode.Requires)]
        public DataTable GetCategories()
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    string cmdText = "select CateID, Name from Category where Disabled = 0 and Name not like 'Other%' order by Name";
                    DataTable table = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, cmdText);
                    return table;
                }
                catch (GDCException gdcEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(gdcEx.StackTrace, gdcEx.Message);
                    throw gdcEx;
                }
                catch (System.Data.SqlClient.SqlException sqlEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(sqlEx.StackTrace, sqlEx.Message); throw new GDCException(ErrorCode.DB_Exception, "DB Exception", sqlEx);
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message); throw new GDCException(ErrorCode.QueryService_Exception, "QueryService Exception", ex);
                }
                finally
                {
                    session.Close();
                }
            }
        }

        /// <summary>
        /// Cluster List
        /// </summary>
        [Transaction(TransactionMode.Requires)]
        public DataTable GetClusters()
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    string cmdText = "select ClusterID, Name from Cluster where Disabled = 0 order by Name";
                    DataTable table = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, cmdText);
                    return table;
                }
                catch (GDCException gdcEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(gdcEx.StackTrace, gdcEx.Message);
                    throw gdcEx;
                }
                catch (System.Data.SqlClient.SqlException sqlEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(sqlEx.StackTrace, sqlEx.Message); throw new GDCException(ErrorCode.DB_Exception, "DB Exception", sqlEx);
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message); throw new GDCException(ErrorCode.QueryService_Exception, "QueryService Exception", ex);
                }
                finally
                {
                    session.Close();
                }
            }
        }

        /// <summary>
        /// Executive List
        /// </summary>
        [Transaction(TransactionMode.Requires)]
        public DataTable GetExecutives()
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    string cmdText = "select Department.DepartmentID ,Employee.NameEN from Department";
                    cmdText += " left join Employee on Department.DepartmentHead = Employee.EmployeeNo";
                    cmdText += " where Department.DepartmentType =5 and Employee.NameEN <>'' order by Employee.NameEN";

                    DataTable table = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, cmdText);
                    return table;
                }
                catch (GDCException gdcEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(gdcEx.StackTrace, gdcEx.Message);
                    throw gdcEx;
                }
                catch (System.Data.SqlClient.SqlException sqlEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(sqlEx.StackTrace, sqlEx.Message); throw new GDCException(ErrorCode.DB_Exception, "DB Exception", sqlEx);
                }
                catch (Exception ex)
                {
                    CCS.Utility.LogAccess.WriteErrLog(ex.StackTrace, ex.Message); throw new GDCException(ErrorCode.QueryService_Exception, "QueryService Exception", ex);
                }
                finally
                {
                    session.Close();
                }
            }
        }

        ///// <summary>
        ///// Executive List
        ///// </summary>
        //public IList BuildCheckBoxASBLeaders()
        //{
        //    using (var dc = new CCEntities())
        //    {
        //        return dc.ASBSeniorLeaders.ToList();
        //    }
        //}

        /// <summary>
        /// delete ASB Leaders 
        /// </summary>
        [Transaction(TransactionMode.Requires)]
        public void DeleteAppASBSenoirLeadersByAppID(int appID)
        {
            using (var dc = new CCEntities())
            {
                var queryLeaders = dc.AppASBSenoirLeaders.Where(p => p.AppID == appID);
                foreach (var leader in queryLeaders)
                {
                    dc.DeleteObject(leader);
                }
                dc.SaveChanges();
            }
        }

        public void SaveASBLeaders(int appID, int ASBLeaderID)
        {
            using (var dc = new CCEntities())
            {
                var e = new AppASBSenoirLeaders();
                e.AppID = appID;
                var ASBLeaders = dc.DicItems.First(p => p.DicItemID == ASBLeaderID);
                e.ASBLeaders = ASBLeaders;
                dc.SaveChanges();
            }
        }

        public IList AppASBLeadersList(int appID)
        {
            using (var dc = new CCEntities())
            {
                return dc.AppASBSenoirLeaders.Include("ASBLeaders").Where(p => p.AppID == appID).ToList();
            }
        }

        public string GetDepartmentByCostCenter(int costcenterID)
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    string cmdText = "  select d.DepartmentCode + ' ' + d.DepartmentNameCN + ' ' + d.DepartmentNameEN from DicItem as di left join CostCenter as cc on di.DicItemKey = cc.CostCenterID left join Department as d on cc.DepartmentID = d.DepartmentID where DicItemID = ";
                    cmdText += costcenterID.ToString();

                    DataTable table = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, cmdText);
                    if (table == null || table.Rows.Count < 1)
                        return "No avaliable department";
                    else
                        return table.Rows[0][0].ToString();
                }
                catch (GDCException gdcEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(gdcEx.StackTrace, gdcEx.Message);
                    return "No avaliable department";
                }
            }
        }

        public string GetDepartmentByCostCenter(string costcentername)
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    string cmdText = string.Format("select d.DepartmentCode + ' ' +  d.DepartmentNameCN + ' ' + d.DepartmentNameEN from CostCenter as cc left join Department as d on cc.DepartmentID = d.DepartmentID where cc.CostCenterID = '{0}'", costcentername);

                    DataTable table = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, cmdText);
                    if (table == null || table.Rows.Count < 1)
                        return "No avaliable department";
                    else
                        return table.Rows[0][0].ToString();
                }
                catch (GDCException gdcEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(gdcEx.StackTrace, gdcEx.Message);
                    return "No avaliable department";
                }
            }
        }

        public string GetEmployeeDetailsByNameEN(string nameen)
        {
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {
                    string cmdText = string.Format("select EmployeeNo + ';' + isnull(OfficePhone,'') + ';' + isnull(Mobile,'') + ';' + isnull(Email,'') from Employee where NameEN = '{0}'", nameen);

                    DataTable table = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, cmdText);
                    if (table == null || table.Rows.Count < 1)
                        return "No avaliable department";
                    else
                        return table.Rows[0][0].ToString();
                }
                catch (GDCException gdcEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(gdcEx.StackTrace, gdcEx.Message);
                    return "No avaliable department";
                }
            }
        }

        public string GetRMBValueFromEuroValue(double euro)
        {
            const string error = "Exchange rate is not available";
            using (ISession session = _sessionManager.OpenSession())
            {
                try
                {

                    double rate;
                    string cmdText = "select Exchange from ExchangeRate where Currency='EURO'";

                    DataTable table = BEGDC.Utility.NHibernate.NHibernateUtil.Execute(session, cmdText);
                    if (table == null || table.Rows.Count < 1)
                        return error;
                    else
                    {
                        if (Double.TryParse(table.Rows[0][0].ToString(), out rate) == false)
                            return error;
                        return (euro * rate).ToString();

                    }
                }
                catch (GDCException gdcEx)
                {
                    CCS.Utility.LogAccess.WriteErrLog(gdcEx.StackTrace, gdcEx.Message);
                    return error;
                }
            }
        }

        public string GetCRNonB(string orgAppNo)
        {
            if (orgAppNo == null) return null;
            if (orgAppNo.IndexOf("CR") == 0 || orgAppNo.IndexOf("OCR") == 0 || orgAppNo.IndexOf("IBG") == 0)
            {
                string appNoResolved = orgAppNo;
                string DashD = "";
                if (appNoResolved.Substring(orgAppNo.Length - 2, 2) == "-D")
                {
                    appNoResolved = orgAppNo.Substring(0, orgAppNo.Length - 2);
                    DashD = "-D";
                }
                if (appNoResolved.Substring(appNoResolved.Length - 2, 2) == "-B")
                {
                    appNoResolved = appNoResolved.Substring(0, appNoResolved.Length - 2);

                }
                return appNoResolved + DashD;
            }
            else
            {
                return orgAppNo;
            }
        }


        #region IUtility Members


        public IList BuildCheckBoxASBLeaders()
        {
            throw new NotImplementedException();
        }

        //public void SaveASBLeaders(int appID, int ASBLeaderID)
        //{
        //    throw new NotImplementedException();
        //}

        //public IList AppASBLeadersList(int appID)
        //{
        //    throw new NotImplementedException();
        //}

        #endregion


        public void SaveLvAmount(string lv2Amount, string lv3Amount, string lv4Amount)
        {
            using (var dc = new CCEntities())
            {
                var amounts = dc.LevelAmount.ToList();
                foreach (var amount in amounts)
                {
                    if (amount.LevelID == 2) amount.Amount = int.Parse(lv2Amount);
                    if (amount.LevelID == 3) amount.Amount = int.Parse(lv3Amount);
                    if (amount.LevelID == 4) amount.Amount = int.Parse(lv4Amount);
                    amount.Description = amount.Amount.ToString() + "元以下授权";
                    dc.SaveChanges();
                }
            }
        }

        public string GetLvAmount(int level)
        {
            using (var dc = new CCEntities())
            {
                return dc.LevelAmount.First(p=>p.LevelID == level).Amount.ToString();
            }
        }
    }
}
